library(foreign)



##HISTORICAL PRECINCT LEVEL NYC CRIME DATA

# # 
# STATISTICAL NOTES
# 1. 2000-2005 Data Source-Historical Comfinal data including Complaint Follow-Up data.  Compiled from aggregated monthly tapes 2000 thru 2005.
# 2. 2006-2015 Data Source-CDW Omniform System and S-DD5 System (Complaint Follow Up) data by record create date.
# 3. Murder & Non-Negligent Manslaughter data source: 2000-2005 Historical Comfinal Data, 2006-2015 Shooting & Homicide Database.
# 4. 2000-2009 data as of 12/8/2010.  2010 data as of 1/18/2011. 2011 data as of 1/18/2012. 2012 data as of 4/1/2013. 2013 data as of 1/17/2014. 2014 data as of 1/16/2015. 2015 data as of 1/18/2016.
# 5. On Sept. 28, 2012, there was a re-alignment of the boundaries of the 077, 078, and 088 precincts.  Therefore statistics for the 077, 078, and 088 precincts following 2011 are not comparable to earlier years.
# 6. The 121 pct was created on 7-1-2013 from parts of the 120 and 122 precinct.  Therefore statistics for 120 and 122 precincts following 2012 are not comparable to earlier years.
# 7. As of 1-1-2014 complaints occurring within the jurisdiction of the Department of Correction have been disaggregated from the precinct crime totals and are denoted in "Pct" column as "DOC".

d<-read.csv("data/seven_major_felony_offenses_by_precinct_2000_2015.csv")
names(d)
names(d)<-tolower(gsub("X","",names(d)))

d$pct<-as.character(d$pct)
d<-d[-which(d$pct=="PCT"),]##remove extra label rows
pcts<-unique(d$pct)
rownames(d)<-1:nrow(d)

for(i in seq(1,624,8)){
	
	pre<-d$pct[i]
	d$pct[(i+1):(i+7)]<-pre
		
}


good.rows<-c("MURDER & NON NEGL. MANSLAUGHTER", "TOTAL SEVEN MAJOR FELONY OFFENSES")

d<-subset(d, d$crime%in%good.rows)
d<-subset(d, d$pct!="DOC")##remove dept of corrections
d<-subset(d, d$pct!="121")##omit brand new precinct, created post-treatment

##murders
dm<-subset(d, d$crime=="MURDER & NON NEGL. MANSLAUGHTER")
dm


##take means between 2008 and 2012
dm$mean.homicide08.12<-NA
dm$mean.homicide08.12 <-rowMeans(dm[,c("2008","2009","2010","2011","2012")], na.rm=T)
dm<-dm[,c("pct","mean.homicide08.12")]
dm


##total felonies
dt<-subset(d, d$crime=="TOTAL SEVEN MAJOR FELONY OFFENSES")
dt

##take means between 2008 and 2012

dt$mean.total.fel08.12<-NA
dt$mean.total.fel08.12 <-rowMeans(dt[,c("2008","2009","2010","2011","2012")], na.rm=T)
dt<-dt[,c("pct","mean.total.fel08.12")]
dt

d<-merge(dm, dt,by="pct")
head(d)

save(d, file="output/precinct_crime.Rdata")

